/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package entity;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import javax.ejb.*;
import javax.naming.*;
import javax.sql.DataSource;
import transfer.CommodityCategory;

/**
 *
 * @author Sergey
 */
public class Category implements EntityBean {

    private EntityContext entityContext;
    private DataSource dataSource;
    private CommodityCategory category = new CommodityCategory();

    public CommodityCategory getCategory() {
        return category;
    }

    public void setCategory(CommodityCategory category) {
        this.category = category;
    }

    public void setEntityContext(EntityContext entityContext) throws EJBException {
        try {
            this.entityContext = entityContext;
            Context context = new InitialContext();
            dataSource = (DataSource) context.lookup("jdbc/ossystem");
        } catch (NamingException ex) {
            throw new EJBException("Does not find jdbc/ossystem");
        }
    }

    public void unsetEntityContext() {
        entityContext = null;
    }

    public void ejbRemove() throws RemoveException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "DELETE FROM Category WHERE id_category = ?");
            statement.setInt(1, category.getIdCategory().intValue());
            if (statement.executeUpdate() < 1) {
                throw new RemoveException("Deleting error");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbActivate() {
    }

    public void ejbPassivate() {
    }

    public void ejbLoad() throws EJBException {
        Integer primaryKey = (Integer) entityContext.getPrimaryKey();
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT name_category, image_category "
                    + "FROM Category WHERE id_category = ?");
            statement.setInt(1, primaryKey.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new NoSuchEntityException("Category with id = " + primaryKey + " not exists");
            }
            String nameCategory = resultSet.getString(1);
            String imageCategory = resultSet.getString(2);
            category.setNameCategory(nameCategory);
            category.setImageCategory(imageCategory);
            category.setIdCategory(primaryKey);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbStore() throws EJBException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "UPDATE Category SET name_category = ?, image_category = ? "
                    + "WHERE id_category = ?");
            statement.setString(1, category.getNameCategory());
            statement.setString(2, category.getImageCategory());
            statement.setInt(3, ((Integer) entityContext.getPrimaryKey()).intValue());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public java.lang.Integer ejbFindByCategory(String aname_category) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        Integer id = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_category "
                    + "FROM Category WHERE name_category = ?");
            statement.setString(1, aname_category);
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("Categoty with this name is not exists");
            } else {
                id = new Integer(resultSet.getInt(1));
            }
            return id;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);;
        }
    }

    public java.lang.Integer ejbFindByPrimaryKey(Integer id_category) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        Integer id = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_category "
                    + "FROM Category WHERE id_category = ?");
            statement.setInt(1, id_category.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("Categoty with this primary Key is not exists");
            } else {
                id = new Integer(resultSet.getInt(1));
            }
            return id;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbPostCreate(CommodityCategory acategory) {
    }

    public java.lang.Integer ejbCreate(CommodityCategory acategory) throws CreateException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            Statement stat = connection.createStatement();
            ResultSet resultSet = stat.executeQuery("select seq.nextval from dual");

            if (resultSet.next()) {
                category.setIdCategory(new Integer(resultSet.getInt(1)));
            }
            stat.close();
            category.setNameCategory(acategory.getNameCategory());
            category.setImageCategory(acategory.getImageCategory());
            statement = connection.prepareStatement("INSERT INTO Category"
                    + "(id_category,name_category, image_category) VALUES(?, ?, ?)");
            statement.setInt(1, category.getIdCategory().intValue());
            statement.setString(2, acategory.getNameCategory());
            statement.setString(3, acategory.getImageCategory());
            if (statement.executeUpdate() != 1) {
                throw new CreateException("Inserted error");
            }
            return category.getIdCategory();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    private void closeConnection(Connection connection, Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public Collection ejbFindCategories(int count) throws FinderException {      
        Connection connection = null;
        PreparedStatement statement = null;
        Collection list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT * FROM (SELECT id_category FROM Category ORDER BY id_category) "
                    + " WHERE rownum <= ?");
            statement.setInt(1, count);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            if (list.isEmpty()) {
                throw new FinderException("Coategories is not exists");
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }    
    }
}
